"""
Case Type   : resource pool
Case Name   : 创建用户关联资源池
Create At   : 2022/08/29
Owner       : songjing20
Description :
    1.预置子class控制组及Workload控制组
    2.创建普通资源池关联workload_a1控制组
    3.创建组资源池关联class_b控制组和业务资源池关联workload_b1
    4.创建用户关联普通资源池
    5.创建用户直接关联业务资源池
    6.创建组用户关联组资源池
    7.创建业务用户关联业务资源池
    8.查询组用户和业务用户关联资源池
    9.验证用户可正常执行业务
    10.环境清理
Expect      :
    1.创建成功
    2.创建成功
    3.创建成功
    4.创建成功
    5.合理报错
    6.成功
    7.成功
    8.成功
    9.执行成功
    10.清理成功
History     :
"""
import os
import unittest

from yat.test import Node
from yat.test import macro
from testcase.utils.Logger import Logger
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant


class ResourcePool(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        self.log.info(f"-----{os.path.basename(__file__)} start-----")
        self.pri_node = Node('PrimaryDbUser')
        self.pri_sh = CommonSH('PrimaryDbUser')
        self.constant = Constant()
        self.class_a = 'class_a'
        self.class_b = 'class_b'
        self.group_a1 = 'workload_a1'
        self.group_a2 = 'workload_a2'
        self.group_b = 'workload_b1'
        self.respool = [f'resp_resource_pool_0060_{i}' for i in range(1, 4)]
        self.user = [f'u_resource_pool_0060_{i}' for i in range(1, 5)]

    def test_resource_pool(self):
        text = "-----step1:预置子class控制组及Workload控制组;expect:创建成功-----"
        self.log.info(text)
        cmd = f"source {macro.DB_ENV_PATH};" \
              f"gs_cgroup -c -S {self.class_a} -s 20; " \
              f"gs_cgroup -c -S {self.class_a} -G {self.group_a1} -g 20; " \
              f"gs_cgroup -c -S {self.class_a} -G {self.group_a2} -g 10; " \
              f"gs_cgroup -c -S {self.class_b} -s 10; " \
              f"gs_cgroup -c -S {self.class_b} -G {self.group_b} -g 60; " \
              f"gs_cgroup -p"
        self.log.info(cmd)
        cmd_res = self.pri_node.sh(cmd).result()
        self.log.info(cmd_res)
        self.assertIn(f"CLASS  Name: {self.class_a}", cmd_res,
                      f'执行失败:{text}')
        self.assertIn(f"CLASS  Name: {self.class_b}", cmd_res,
                      f'执行失败:{text}')
        self.assertIn(f"DEFWD  Name: {self.group_a1}", cmd_res,
                      f'执行失败:{text}')
        self.assertIn(f"DEFWD  Name: {self.group_a2}", cmd_res,
                      f'执行失败:{text}')
        self.assertIn(f"DEFWD  Name: {self.group_b}", cmd_res,
                      f'执行失败:{text}')

        text = "-----step2:创建普通资源池关联workload_a1控制组;expect:创建成功-----"
        self.log.info(text)
        sql_res = self.pri_sh.execut_db_sql(
            f"create resource pool {self.respool[0]} "
            f"with(control_group = '{self.class_a}:{self.group_a1}');")
        self.log.info(sql_res)
        self.assertEqual(self.constant.create_resource_pool_success, sql_res,
                         f'执行失败:{text}')

        text = "-----step3:创建组资源池关联class_b控制组和业务资源池关联workload_b1;" \
               "expect:创建成功-----"
        self.log.info(text)
        sql_res = self.pri_sh.execut_db_sql(
            f"create resource pool {self.respool[1]} "
            f"with(control_group = '{self.class_b}');"
            f"create resource pool {self.respool[2]} "
            f"with(control_group = '{self.class_b}:{self.group_b}');")
        self.log.info(sql_res)
        self.assertEqual(2, sql_res.count(
            self.constant.create_resource_pool_success), f'执行失败:{text}')

        text = "-----step4:创建用户关联普通资源池;expect:成功-----"
        self.log.info(text)
        sql_res = self.pri_sh.execut_db_sql(
            f"create user {self.user[0]} resource pool '{self.respool[0]}' "
            f"password '{macro.COMMON_PASSWD}';")
        self.log.info(sql_res)
        self.assertIn(self.constant.CREATE_ROLE_SUCCESS_MSG,
                      sql_res, f'执行失败:{text}')

        text = "-----step5:创建用户直接关联业务资源池;expect:创建失败,合理报错-----"
        self.log.info(text)
        sql_res = self.pri_sh.execut_db_sql(
            f"create user {self.user[3]} resource pool '{self.respool[2]}' "
            f"password '{macro.COMMON_PASSWD}';")
        self.log.info(sql_res)
        self.assertIn('ERROR:  please specify user group when create or alter '
                      'user with child resource pool', sql_res, f'执行失败:{text}')

        text = "-----step6:创建组用户关联组资源池;expect:成功-----"
        self.log.info(text)
        sql_res = self.pri_sh.execut_db_sql(
            f"create user {self.user[1]} resource pool '{self.respool[1]}' "
            f"password '{macro.COMMON_PASSWD}';")
        self.log.info(sql_res)
        self.assertIn(self.constant.CREATE_ROLE_SUCCESS_MSG,
                      sql_res, f'执行失败:{text}')

        text = "-----step7:创建业务用户关联业务资源池;expect:成功-----"
        self.log.info(text)
        sql_res = self.pri_sh.execut_db_sql(
            f"create user {self.user[2]} resource pool '{self.respool[2]}' "
            f"user group '{self.user[1]}' password '{macro.COMMON_PASSWD}';")
        self.log.info(sql_res)
        self.assertIn(self.constant.CREATE_ROLE_SUCCESS_MSG,
                      sql_res, f'执行失败:{text}')

        text = "-----step8:查询组用户和业务用户关联资源池;expect:成功-----"
        self.log.info(text)
        sql_res = self.pri_sh.execut_db_sql(
            f"select usename, respool from pg_user "
            f"where usename like '{self.user[0][:-2]}%' order by 1;")
        self.log.info(sql_res)
        self.assertIn(f"{self.user[0]} | {self.respool[0]}\n "
                      f"{self.user[1]} | {self.respool[1]}\n "
                      f"{self.user[2]} | {self.respool[2]}\n"
                      f"(3 rows)", sql_res, f'执行失败:{text}')

        text = "-----step9:验证用户可正常执行业务;expect:成功-----"
        self.log.info(text)
        sql = f'''drop table if exists t_resource_pool_0060 cascade;
        create table t_resource_pool_0060(c_int int primary key, 
          c_vchar varchar(20), c_bigint bigint, c_date date);
        insert into t_resource_pool_0060 values(generate_series(1,1000000), 
          'test'|| generate_series(1,1000000), 12345698, date'2022-6-4');
        analyze t_resource_pool_0060;
        create index i_resource_pool_0060 on t_resource_pool_0060(c_vchar);
        select count(*) from t_resource_pool_0060 where c_int>5000;
        truncate table t_resource_pool_0060;
        vacuum t_resource_pool_0060;
        drop table if exists t_resource_pool_0060 cascade;'''
        for user in self.user[:-1]:
            self.log.info(f"----- 用户 {user} 执行业务-----")
            sql_res = self.pri_sh.execut_db_sql(
                sql, sql_type=f'-U {user} -W{macro.COMMON_PASSWD}')
            self.log.info(sql_res)
            self.assertIn(self.constant.ANALYZE_SUCCESS_MSG, sql_res,
                          f'执行失败:{text}')
            self.assertIn(self.constant.CREATE_INDEX_SUCCESS, sql_res,
                          f'执行失败:{text}')
            self.assertIn('995000\n(1 row)', sql_res, f'执行失败:{text}')
            self.assertIn(self.constant.TRUNCATE_SUCCESS_MSG, sql_res,
                          f'执行失败:{text}')
            self.assertIn(self.constant.VACUUM_SUCCESS_MSG, sql_res,
                          f'执行失败:{text}')
            self.assertIn(self.constant.DROP_TABLE_SUCCESS,
                          sql_res.splitlines()[-1], f'执行失败:{text}')

    def tearDown(self):
        text = "-----step10:环境清理;expect:成功-----"
        self.log.info(text)
        drop_user = self.pri_sh.execut_db_sql(
            '\n'.join(
                [f"drop user if exists {i} cascade;" for i in self.user]))
        self.log.info(drop_user)
        drop_resp_res = self.pri_sh.execut_db_sql(
            '\n'.join(
                [f"drop resource pool if exists {i};" for i in self.respool]))
        self.log.info(drop_resp_res)
        cmd = f"source {macro.DB_ENV_PATH};gs_cgroup --revert"
        self.log.info(cmd)
        cmd_res = self.pri_node.sh(cmd).result()
        self.assertEqual(drop_user.count(self.constant.DROP_ROLE_SUCCESS_MSG),
                         4, f'执行失败:{text}')
        self.assertEqual(3, drop_resp_res.count(
            self.constant.drop_resource_pool_success), f'执行失败:{text}')
        self.assertEqual('', cmd_res, f'执行失败:{text}')
        self.log.info(f"-----{os.path.basename(__file__)} end-----")
